package com.zkh.myutils.scheduled;

import com.zkh.myutils.database.dbpool.DBType;

/**
 * 定时任务相关表操作SQL。因为只在初始化时调用，所以没必要定义为静态变量来占用内存。
 * @author zkh
 */
public class ScheduledTaskTableDDL {
	//Oracle创建表SQL
	private String[] oracleDDL = {"declare\r\n"
			+ "      num number;\r\n"
			+ "begin\r\n"
			+ "    SELECT count(1) into num FROM USER_TABLES WHERE TABLE_NAME = upper('{prefix}timer_task_def');\r\n"
			+ "    if num = 0 then\r\n"
			+ "        execute immediate 'CREATE TABLE {prefix}timer_task_def (  \r\n"
			+ "          jobEcd VARCHAR(50) NOT NULL,\r\n"
			+ "          jobDesc VARCHAR2(300) NOT NULL,\r\n"
			+ "          cronExp VARCHAR(50) NOT NULL,\r\n"
			+ "          jobBeanName VARCHAR(50) NOT NULL,\r\n"
			+ "          jobParam clob NOT NULL,\r\n"
			+ "          createTime timestamp(3) NOT NULL\r\n"
			+ "        )';\r\n"
			+ "        execute immediate 'alter table {prefix}timer_task_def add constraint pk_{prefix}timer_task_def primary key(jobEcd)';\r\n"
			+ "        execute immediate 'comment on column {prefix}timer_task_def.jobEcd is ''作业编码''';\r\n"
			+ "        execute immediate 'comment on column {prefix}timer_task_def.jobDesc is ''作业描述''';\r\n"
			+ "        execute immediate 'comment on column {prefix}timer_task_def.cronExp is ''cron表达式''';\r\n"
			+ "        execute immediate 'comment on column {prefix}timer_task_def.jobBeanName is ''作业Bean名称''';\r\n"
			+ "        execute immediate 'comment on column {prefix}timer_task_def.jobParam is ''作业参数''';\r\n"
			+ "        execute immediate 'comment on column {prefix}timer_task_def.createTime is ''创建时间''';\r\n"
			+ "    end if;\r\n"
			+ "    SELECT count(1) into num FROM USER_TABLES WHERE TABLE_NAME = upper('{prefix}timer_task_record');\r\n"
			+ "    if num = 0 then\r\n"
			+ "       execute immediate 'CREATE TABLE {prefix}timer_task_record (  \r\n"
			+ "         jobEcd VARCHAR(50) NOT NULL,\r\n"
			+ "         executeTime number(14) NOT NULL,\r\n"
			+ "         executeType number(1) NOT NULL,\r\n"
			+ "         startTime timestamp(3) NOT NULL,\r\n"
			+ "         execParam clob,\r\n"
			+ "         endTime timestamp(3),\r\n"
			+ "         execState number(1) NOT NULL,\r\n"
			+ "         execResult clob\r\n"
			+ "       )';\r\n"
			+ "       execute immediate 'alter table {prefix}timer_task_record add constraint pk_{prefix}timer_task_record primary key(jobEcd, executeTime)';\r\n"
			+ "       execute immediate 'comment on column {prefix}timer_task_record.jobEcd is ''作业编码''';\r\n"
			+ "       execute immediate 'comment on column {prefix}timer_task_record.executeTime is ''执行点''';\r\n"
			+ "       execute immediate 'comment on column {prefix}timer_task_record.executeType is ''执行类型。0：自动，1：手动''';\r\n"
			+ "       execute immediate 'comment on column {prefix}timer_task_record.startTime is ''开始执行时间''';\r\n"
			+ "       execute immediate 'comment on column {prefix}timer_task_record.execParam is ''执行参数''';\r\n"
			+ "       execute immediate 'comment on column {prefix}timer_task_record.endTime is ''执行结束时间''';\r\n"
			+ "       execute immediate 'comment on column {prefix}timer_task_record.execState is ''执行状态。1：执行中；2：执行成功；3：执行失败''';\r\n"
			+ "       execute immediate 'comment on column {prefix}timer_task_record.execResult is ''执行结果''';\r\n"
			+ "    end if;\r\n"
			+ "end;"};
	//MySQL创建表SQL
	private String[] mysqlDDL = {"CREATE TABLE IF NOT EXISTS `{prefix}timer_task_def` (  \r\n"
			+ "  `jobEcd` VARCHAR(50) NOT NULL COMMENT '作业编码',\r\n"
			+ "  `jobDesc` VARCHAR(100) NOT NULL COMMENT '作业描述',\r\n"
			+ "  `cronExp` VARCHAR(50) NOT NULL COMMENT 'cron表达式',\r\n"
			+ "  `jobBeanName` VARCHAR(50) NOT NULL COMMENT '作业Bean名称',\r\n"
			+ "  `jobParam` TEXT NOT NULL COMMENT '作业参数',\r\n"
			+ "  `createTime` DATETIME NOT NULL COMMENT '创建时间',\r\n"
			+ "  PRIMARY KEY (`jobEcd`) \r\n"
			+ ") ENGINE=INNODB CHARSET=UTF8MB4;\r\n"
			,
			"CREATE TABLE IF NOT EXISTS `{prefix}timer_task_record` (  \r\n"
			+ "  `jobEcd` VARCHAR(50) NOT NULL COMMENT '作业编码',\r\n"
			+ "  `executeTime` BIGINT NOT NULL COMMENT '执行点',\r\n"
			+ "  `executeType` INT NOT NULL COMMENT '执行类型。0：自动，1：手动',\r\n"
			+ "  `startTime` DATETIME NOT NULL COMMENT '开始执行时间',\r\n"
			+ "  `execParam` TEXT COMMENT '执行参数',\r\n"
			+ "  `endTime` DATETIME COMMENT '执行结束时间',\r\n"
			+ "  `execState` INT NOT NULL COMMENT '执行状态。1：执行中；2：执行成功；3：执行失败',\r\n"
			+ "  `execResult` TEXT COMMENT '执行结果',\r\n"
			+ "  PRIMARY KEY (`jobEcd` , `executeTime`) \r\n"
			+ ") ENGINE=INNODB CHARSET=UTF8MB4"};
	//sqlServer创建表SQL
	private String[] sqlServerDDL = {"if not exists (select * from sysobjects where id = object_id('{prefix}timer_task_def') and OBJECTPROPERTY(id,'IsUserTable')=1)\r\n"
			+ "CREATE TABLE {prefix}timer_task_def (  \r\n"
			+ "  jobEcd VARCHAR(50) NOT NULL PRIMARY KEY,\r\n"
			+ "  jobDesc VARCHAR(100) NOT NULL,\r\n"
			+ "  cronExp VARCHAR(50) NOT NULL,\r\n"
			+ "  jobBeanName VARCHAR(50) NOT NULL,\r\n"
			+ "  jobParam TEXT NOT NULL,\r\n"
			+ "  createTime DATETIME NOT NULL\r\n"
			+ ");"
			,
			"if not exists (select * from sysobjects where id = object_id('{prefix}timer_task_record') and OBJECTPROPERTY(id,'IsUserTable')=1)\r\n"
			+ "CREATE TABLE {prefix}timer_task_record (  \r\n"
			+ "  jobEcd VARCHAR(50) NOT NULL,\r\n"
			+ "  executeTime BIGINT NOT NULL,\r\n"
			+ "  executeType INT NOT NULL,\r\n"
			+ "  startTime DATETIME NOT NULL,\r\n"
			+ "  execParam TEXT,\r\n"
			+ "  endTime DATETIME,\r\n"
			+ "  execState INT NOT NULL,\r\n"
			+ "  execResult TEXT,\r\n"
			+ "  PRIMARY KEY (jobEcd , executeTime)\r\n"
			+ ");"};
	//Oracle创建表SQL
	private String inserSQL = "insert into {prefix}timer_task_def (jobEcd, jobDesc, cronExp, jobBeanName, jobParam, createTime) values (?, ?, ?, ?, ?, ?)";
	
	/**
	 * 获取表DDL语句
	 * @param type 数据库类型
	 */
	public static String[] getTableDDL(DBType type) {
		//初始化
		ScheduledTaskTableDDL ddl = new ScheduledTaskTableDDL();
		//获取对应SQL
		if(DBType.TYPE_MYSQL==type) {
			return ddl.mysqlDDL;
		}else if(DBType.TYPE_SQLSERVER==type) {
			return ddl.sqlServerDDL;
		}else {
			return ddl.oracleDDL;
		}
	}
	
	/**
	 * 获取insert语句
	 */
	public static String getInsertSQL() {
		return new ScheduledTaskTableDDL().inserSQL;
	}
}
